package com.Ngbussiness.dao.impl;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.Ngbussiness.dao.yongjinDao;
import com.Ngbussiness.util.Calenda;
import com.Ngbussiness.util.DatabaseAccess;
import com.Ngbussiness.util.EasyMapsManager;
import com.Ngbussiness.util.MD5;


public class yongjinDaoImpl implements yongjinDao{
	
	public List getfiveyongjin(double longitude, double latitude,int canshu){
		DatabaseAccess dao=new DatabaseAccess();
		String sql="";
		if(canshu==0){
			sql="select sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI" +
					"()*12656*cos (((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()" +
					"*12656/180))) " +
					"as distance,Id,businessname,discription,img_url,longitude,latitude from yongjin where sqrt((((?-longitude)*" +
					"PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos (((?+latitude)/2)*PI()/180)/180))+" +
					"(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180)))<10";
			 try{
					DatabaseAccess databaseAccess = new DatabaseAccess(false);
					EasyMapsManager easyMapsManager = new EasyMapsManager(databaseAccess);
					easyMapsManager.setPreparedParameter(longitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(longitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(longitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(longitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(latitude);
					List list = easyMapsManager.executeQuery(sql);
					databaseAccess.close();
					return list;
				}catch(Exception e) {
					e.printStackTrace();
				}
		}
		else{
			   sql="select sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI" +
					"()*12656*cos (((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()" +
					"*12656/180))) " +
					"as distance,Id,businessname,discription,img_url,longitude,latitude from yongjin order by distance asc limit 0,5;";
			   try{
					DatabaseAccess databaseAccess = new DatabaseAccess(false);
					EasyMapsManager easyMapsManager = new EasyMapsManager(databaseAccess);
					easyMapsManager.setPreparedParameter(longitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(longitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(latitude);
					easyMapsManager.setPreparedParameter(latitude);
					List list = easyMapsManager.executeQuery(sql);
					databaseAccess.close();
					return list;
				}catch(Exception e) {
					e.printStackTrace();
				}
		}
		return null;
	}
	
	public List getbsinformation(int yongjinid){
			DatabaseAccess dao=new DatabaseAccess();
			String sql="select Id,phone,sjaddress,bushour,discription,is_ok,first_mcid,second_mcid,businessname," +
					"img_url,longitude,latitude from yongjin where Id=?;";
			try{
				DatabaseAccess databaseAccess = new DatabaseAccess(false);
				EasyMapsManager easyMapsManager = new EasyMapsManager(databaseAccess);
				easyMapsManager.setPreparedParameter(yongjinid);
				List list = easyMapsManager.executeQuery(sql);
				HashMap adu=(HashMap)list.get(0);
				String second_mcid=adu.get("second_mcid").toString();
				int first_mcid=Integer.parseInt(adu.get("first_mcid").toString());
				String[] ss=second_mcid.split(",,");
				List list2=new ArrayList();
				String result="";
				String result1="";
				for(int i=0;i<ss.length;i++){
					if(i==0){
						ss[i]=ss[i].substring(1,ss[i].length());
					}
					if(i==ss.length-1){
						ss[i]=ss[i].substring(0,ss[i].length()-1);
					}
					String sql1="select name from modul_category where Id=?";
					try{
						DatabaseAccess databaseAccess1 = new DatabaseAccess(false);
						EasyMapsManager easyMapsManager1 = new EasyMapsManager(databaseAccess1);
						easyMapsManager1.setPreparedParameter(ss[i]);
						list2 = easyMapsManager1.executeQuery(sql1);
						HashMap adu1=(HashMap)list2.get(0);
						String name=adu1.get("name").toString();
						if(i!=ss.length-1){
						result=result+name+",";
						}
						else{
							result=result+name;
						}
						databaseAccess1.close();
					}
					catch(Exception e) {
						e.printStackTrace();
					}
				}
				adu.put("second_mcid",result);
				String sql3="select name from category where Id=?;";
				try{
					DatabaseAccess databaseAccess2 = new DatabaseAccess(false);
					EasyMapsManager easyMapsManager2 = new EasyMapsManager(databaseAccess2);
					easyMapsManager2.setPreparedParameter(first_mcid);
					List list3 = easyMapsManager2.executeQuery(sql3);
					HashMap adu2=(HashMap)list3.get(0);
					result1=adu2.get("name").toString();
					databaseAccess2.close();
				}
				catch(Exception e) {
					e.printStackTrace();
				}
				adu.put("first_mcid", result1);
				databaseAccess.close();
				return list;
			}catch(Exception e) {
				e.printStackTrace();
			}
			return null;
	}
	
	public boolean insertyj(String start_time, String end_time, String phone,
			String sjaddress, int range, double price, int num, String img_url,
			double probability, String businessname, String discription,
			int category1, String category2,double longitude,double latitude,int member_id,String bushour){
		DatabaseAccess dao=new DatabaseAccess();
		String sql="insert into yongjin(first_mcid,second_mcid,member_id,price,num,is_ok,start_time,end_time,probability," +
				"phone,longitude,latitude,is_close,img_url,businessname,discription,range1,bushour,sjaddress) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		dao.setPreparedParameter(category1);
		dao.setPreparedParameter(category2);
		dao.setPreparedParameter(member_id);
		dao.setPreparedParameter(price);
		dao.setPreparedParameter(num);
		dao.setPreparedParameter(0);
		dao.setPreparedParameter(start_time);
		dao.setPreparedParameter(end_time);
		dao.setPreparedParameter(probability);
		dao.setPreparedParameter(phone);
		dao.setPreparedParameter(longitude);
		dao.setPreparedParameter(latitude);
		dao.setPreparedParameter(0);
		dao.setPreparedParameter(img_url);
		dao.setPreparedParameter(businessname);
		dao.setPreparedParameter(discription);
		dao.setPreparedParameter(range);
		dao.setPreparedParameter(bushour);
		dao.setPreparedParameter(sjaddress);
		boolean b=dao.executeInsert(sql)>0?true:false;
		return b;
	}
	public List getnearbus(int page,int distance, double longitude, double latitude,
			int category1, int category2,int rows){
		double distance1=distance/1000;
        String sql="select Id,businessname,img_url,second_mcid,first_mcid,longitude,latitude,is_ok from yongjin where is_close=0 and ";
		if(distance==0){
			if(category1==0){
				if(category2==0){
					sql = sql + "first_mcid<>? and second_mcid not like '%,"+category2+",%' order by sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
							"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180))) asc";
					System.out.println("1");
				}
				else
					sql = sql + "and first_mcid<>? and second_mcid like '%,"+category2+",%' order by sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
							"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180))) asc";
				System.out.println("2");
			}
			else
			{
				if(category2==0){
					sql= sql + "first_mcid=? and second_mcid not like '%,"+category2+",%' order by sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
							"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180))) asc";
					System.out.println("3");
				}
				else
					sql= sql +"first_mcid=? and second_mcid like '%,"+category2+",%' order by sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
							"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180))) asc";
				System.out.println("4");
			}
		}
		else if(category1==0){
			if(category2==0){
				sql = sql + "first_mcid<>? and second_mcid not like '%,"+category2+",%' and sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
						"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180)))<"+distance1+"";
				System.out.println("5");
			}
			else
			{
				sql = sql +"first_mcid<>? and second_mcid like '%,"+category2+",%' and sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
				"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180)))<"+distance1+"";
				System.out.println("6");
			}
		}
		else if(category2==0){
			sql = sql + "first_mcid=? and second_mcid not like '%,"+category2+",%' and sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
					"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180)))<"+distance1+"";
			System.out.println("7");
		}
		else
		{
			sql = sql + "first_mcid=? and second_mcid like '%,"+category2+",%' and sqrt((((?-longitude)*PI()*12656*cos(((?+latitude)/2)*PI()/180)/180)*((?-longitude)*PI()*12656*cos " +
					"(((?+latitude)/2)*PI()/180)/180))+(((?-latitude)*PI()*12656/180)*((?-latitude)*PI()*12656/180)))<"+distance1+"";
			System.out.println("8");
		}
		sql=sql+" limit ?,?";
		System.out.println(sql);
		try{
			DatabaseAccess databaseAccess = new DatabaseAccess(false);
			EasyMapsManager easyMapsManager = new EasyMapsManager(databaseAccess);
			easyMapsManager.setPreparedParameter(category1);
			easyMapsManager.setPreparedParameter(longitude);
			easyMapsManager.setPreparedParameter(latitude);
			easyMapsManager.setPreparedParameter(longitude);
			easyMapsManager.setPreparedParameter(latitude);
			easyMapsManager.setPreparedParameter(latitude);
			easyMapsManager.setPreparedParameter(latitude);
			easyMapsManager.setPreparedParameter(rows*(page-1));
			easyMapsManager.setPreparedParameter(rows);
			List list = easyMapsManager.executeQuery(sql);
			for(int k=0;k<list.size();k++){
			HashMap adu=(HashMap)list.get(k);
			String second_mcid=adu.get("second_mcid").toString();
			int first_mcid=Integer.parseInt(adu.get("first_mcid").toString());
			String[] ss=second_mcid.split(",,");
			List list2=new ArrayList();
			String result="";
			String result1="";
			for(int i=0;i<ss.length;i++){
				if(i==0){
					ss[i]=ss[i].substring(1,ss[i].length());
				}
				if(i==ss.length-1){
					ss[i]=ss[i].substring(0,ss[i].length()-1);
				}
				String sql1="select name from modul_category where Id=?";
				try{
					DatabaseAccess databaseAccess1 = new DatabaseAccess(false);
					EasyMapsManager easyMapsManager1 = new EasyMapsManager(databaseAccess1);
					easyMapsManager1.setPreparedParameter(ss[i]);
					list2 = easyMapsManager1.executeQuery(sql1);
					HashMap adu1=(HashMap)list2.get(0);
					String name=adu1.get("name").toString();
					if(i!=ss.length-1){
					result=result+name+",";
					}
					else{
						result=result+name;
					}
					databaseAccess1.close();
				}
				catch(Exception e) {
					e.printStackTrace();
				}
			}
			adu.put("second_mcid",result);
			String sql3="select name from category where Id=?;";
			try{
				DatabaseAccess databaseAccess2 = new DatabaseAccess(false);
				EasyMapsManager easyMapsManager2 = new EasyMapsManager(databaseAccess2);
				easyMapsManager2.setPreparedParameter(first_mcid);
				List list3 = easyMapsManager2.executeQuery(sql3);
				HashMap adu2=(HashMap)list3.get(0);
				result1=adu2.get("name").toString();
				databaseAccess2.close();
			}
			catch(Exception e) {
				e.printStackTrace();
			}
			adu.put("first_mcid", result1);
			}
			databaseAccess.close();
			return list;
		}catch(Exception e) {
			e.printStackTrace();
		}
	return null;
	}

	public List gettenyongjin(int page, int category1id, int category2id,
			int rows){
	       String sql="select Id,businessname,is_ok,img_url,second_mcid,first_mcid,longitude,latitude from yongjin where is_close=0 and ";
				if(category1id==0){
					if(category2id==0){
						sql = sql + "first_mcid<>? and second_mcid not like '%,"+category2id+",%'";
						System.out.println("1");
					}
					else{
						sql = sql + "and first_mcid<>? and second_mcid like '%,"+category2id+",%'";
					System.out.println("2");
					}
				}
				else
				{
					if(category2id==0){
						sql= sql + "first_mcid=? and second_mcid not like '%,"+category2id+",%'";
						System.out.println("3");
					}
					else{
						sql= sql +"first_mcid=? and second_mcid like '%,"+category2id+",%'";
					System.out.println("4");
					}
				}
				sql=sql+" LIMIT ?,?;";
			System.out.println(sql);
			try{
				DatabaseAccess databaseAccess = new DatabaseAccess(false);
				EasyMapsManager easyMapsManager = new EasyMapsManager(databaseAccess);
				easyMapsManager.setPreparedParameter(category1id);
				easyMapsManager.setPreparedParameter(rows*(page-1));
				easyMapsManager.setPreparedParameter(rows);
				List list = easyMapsManager.executeQuery(sql);
				System.out.println(list);
				for(int k=0;k<list.size();k++){
				HashMap adu=(HashMap)list.get(k);
				String second_mcid=adu.get("second_mcid").toString();
				int first_mcid=Integer.parseInt(adu.get("first_mcid").toString());
				String[] ss=second_mcid.split(",,");
				List list2=new ArrayList();
				String result="";
				String result1="";
				for(int i=0;i<ss.length;i++){
					if(i==0){
						ss[i]=ss[i].substring(1,ss[i].length());
					}
					if(i==ss.length-1){
						ss[i]=ss[i].substring(0,ss[i].length()-1);
					}
					String sql1="select name from modul_category where Id=?";
					try{
						DatabaseAccess databaseAccess1 = new DatabaseAccess(false);
						EasyMapsManager easyMapsManager1 = new EasyMapsManager(databaseAccess1);
						easyMapsManager1.setPreparedParameter(ss[i]);
						list2 = easyMapsManager1.executeQuery(sql1);
						HashMap adu1=(HashMap)list2.get(0);
						String name=adu1.get("name").toString();
						if(i!=ss.length-1){
						result=result+name+",";
						}
						else{
							result=result+name;
						}
						databaseAccess1.close();
					}
					catch(Exception e) {
						e.printStackTrace();
					}
				}
				adu.put("second_mcid",result);
				String sql3="select name from category where Id=?;";
				try{
					DatabaseAccess databaseAccess2 = new DatabaseAccess(false);
					EasyMapsManager easyMapsManager2 = new EasyMapsManager(databaseAccess2);
					easyMapsManager2.setPreparedParameter(first_mcid);
					List list3 = easyMapsManager2.executeQuery(sql3);
					HashMap adu2=(HashMap)list3.get(0);
					result1=adu2.get("name").toString();
					databaseAccess2.close();
				}
				catch(Exception e) {
					e.printStackTrace();
				}
				adu.put("first_mcid", result1);
				}
				databaseAccess.close();
				return list;
			}catch(Exception e) {
				e.printStackTrace();
			}
		return null;
	}
}
